﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using EvaluacionExalmarOledbHelper;
using EvaluacionExalmarEntidades;
using EvaluacionExalmarInterfaces;
using System.Configuration;
using System.Text;

namespace EvaluacionExalmarAccesoDatos
{
    public class Area : IArea
    {
        #region IArea Members

        private readonly String _sCadenaConexion =
ConfigurationManager.ConnectionStrings["con"].ConnectionString;

        public AreaInfo Consultar(AreaInfo oArea)
        {
            throw new NotImplementedException();
        }

        public IList<AreaInfo> Listar(AreaInfo oArea)
        {
             string query = "Select Id_Area, Descripcion, Id_Gerencia, Gerencia from ConsultaAreas";
            var oListaArea = new List<AreaInfo>();

            if (oArea != null)
            {
                var where = new StringBuilder(" where ");
                if (oArea.IdGerencia.HasValue)
                    where.Append(" Id_Gerencia =" + oArea.IdGerencia + " ");

                query += @where;
            }

            using (var drd = OleDbHelper.ExecuteReader(_sCadenaConexion, CommandType.Text, query))
            {
                if (drd != null)
                {
                    if (drd.HasRows)
                    {
                        while (drd.Read())
                        {
                            oListaArea.Add(new AreaInfo(drd.GetInt32(0), drd.GetString(1).Trim(),
                                drd.GetInt32(2), new GerenciaInfo(drd.GetInt32(2), drd.GetString(3).Trim())));
                        }
                    }
                }
            }
            return oListaArea;
        }

        public bool Registrar(AreaInfo oArea)
        {
            bool bExito;

            const string query = "Insert into Area (Descripcion, Id_Gerencia) Values (?, ?)";

            var oledbParm = new OleDbParameter[2];

            using (var oledbcon = new OleDbConnection(_sCadenaConexion))
            {
                oledbcon.Open();
                var oledbtran = oledbcon.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    oledbParm[0] = new OleDbParameter("@Descripcion", OleDbType.VarChar) { Value = oArea.Descripcion };
                    oledbParm[1] = new OleDbParameter("@Id_Gerencia", OleDbType.Integer) { Value = oArea.IdGerencia };

                    OleDbHelper.ExecuteNonQuery(oledbtran, CommandType.Text, query, oledbParm);
                    bExito = true;
                    oledbtran.Commit();
                }
                catch
                {
                    oledbtran.Rollback();
                    bExito = false;
                }
                finally
                {
                    oledbcon.Close();
                }
            }


            return bExito;
        }

        public bool Actualizar(AreaInfo oArea)
        {
            bool bExito;

            const string query = "Update Area " +
                                 " Set Descripcion = ?, Id_Gerencia = ? Where Id_Area = ?";

            var oledbParm = new OleDbParameter[3];

            using (var oledbcon = new OleDbConnection(_sCadenaConexion))
            {
                oledbcon.Open();
                var oledbtran = oledbcon.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    oledbParm[0] = new OleDbParameter("@Descripcion", OleDbType.VarChar) { Value = oArea.Descripcion };
                    oledbParm[1] = new OleDbParameter("@Id_Gerencia", OleDbType.Integer) { Value = oArea.IdGerencia };
                    oledbParm[2] = new OleDbParameter("@Id_Area", OleDbType.Integer) { Value = oArea.IdArea };

                    OleDbHelper.ExecuteNonQuery(oledbtran, CommandType.Text, query, oledbParm);
                    bExito = true;
                    oledbtran.Commit();
                }
                catch
                {
                    oledbtran.Rollback();
                    bExito = false;
                }
                finally
                {
                    oledbcon.Close();
                }
            }


            return bExito;
        }

        #endregion
    }
}
